Teiid supports non-recursive common table expressions via the WITH clause.  WITH clause items may be referenced as tables in subsequent with clause items and in the main query. The WITH clause can be thought of as providing query scoped temporary tables.

Usage:

WITH name [(column, ...)] AS (query expression) ...

Syntax Rules:

• All of the projected column names must be unique. If they are not unique, then the column name list must be provided.

• If the columns of the WITH clause item are declared, then they must match the number of columns projected by the query expression.

• Each with clause item must have a unique name.